/****** Object:  Stored Procedure dbo.CGO_TriviaImportData    Script Date: Monday, March 07, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CGO_TriviaImportData') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.CGO_TriviaImportData
GO

/* -------------------------------------------------------------------------------------
/   AccountGet
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.CGO_TriviaImportData	
AS

DECLARE @ImportID INT
DECLARE @CategoryID INT
DECLARE @QuestionID INT
DECLARE @CorrectAnswerID INT
DECLARE @CategoryName NVARCHAR(255)
DECLARE @AnswerDesc NVARCHAR(255)

WHILE EXISTS (SELECT 1 FROM dbo.CGO_TriviaQuestionIMP WHERE Imported IS NULL)
BEGIN
  SELECT  @ImportID = ImportID
  FROM    ( SELECT TOP 1
                    ImportID
            FROM    dbo.CGO_TriviaQuestionIMP
            WHERE   Imported IS NULL
            ORDER BY ImportID ASC
          ) T
  
  SELECT  @CategoryName = Category, @QuestionID = QuestionID
  FROM    dbo.CGO_TriviaQuestionIMP
  WHERE   ImportID = @ImportID          
  
  --Get or Generate the @CategoryID
  IF EXISTS (SELECT 1 FROM dbo.CGO_TriviaCategory WHERE CategoryName=@CategoryName)
  BEGIN
    SELECT @CategoryID = CategoryID FROM dbo.CGO_TriviaCategory WHERE CategoryName = @CategoryName
  END
  ELSE
  BEGIN
  	INSERT INTO dbo.CGO_TriviaCategory
  	( CategoryName )
  	VALUES  
  	( @CategoryName)
  	SET @CategoryID = @@IDENTITY  	        	        
  END
  
  IF @QuestionID IS NULL
  BEGIN
    INSERT INTO dbo.CGO_TriviaQuestion
    ( CategoryID ,
      Description ,
      CorrectAnswerID ,
      Difficulty ,
      Comment      
    )
    SELECT  @CategoryID ,
            Question ,
            NULL ,            
            Difficulty,
            Comment
    FROM    dbo.CGO_TriviaQuestionIMP
    WHERE   ImportID = @ImportID
    SET @QuestionID = @@IDENTITY          
  END
  ELSE
  BEGIN
    UPDATE dbo.CGO_TriviaQuestion
    SET    CategoryID = @CategoryID ,
          Description = I.Question ,
          CorrectAnswerID = NULL ,
          Difficulty = I.Difficulty ,
          Comment = I.Comment
    FROM   dbo.CGO_TriviaQuestion M
          INNER JOIN dbo.CGO_TriviaQuestionIMP I ON M.QuestionID = I.QuestionID
    WHERE  I.ImportID = @ImportID
  END
  
  IF @QuestionID IS NOT NULL
  BEGIN
  	DELETE FROM dbo.CGO_TriviaAnswer
  	WHERE QuestionID = @QuestionID
  	
    INSERT INTO dbo.CGO_TriviaAnswer
    ( QuestionID ,Description )
    SELECT  @QuestionID ,
            CorrectAnswer
    FROM    dbo.CGO_TriviaQuestionIMP
    WHERE   ImportID = @ImportID
    
    SET @CorrectAnswerID = @@IDENTITY
    
    UPDATE dbo.CGO_TriviaQuestion
    SET CorrectAnswerID = @CorrectAnswerID
    WHERE QuestionID = @QuestionID
    
    
    INSERT  INTO dbo.CGO_TriviaAnswer
    ( QuestionID ,Description )
    SELECT  @QuestionID ,
            AnswerB
    FROM    dbo.CGO_TriviaQuestionIMP
    WHERE   ImportID = @ImportID
            AND AnswerB IS NOT NULL
            AND AnswerB <> ''
    UNION
    SELECT  @QuestionID ,
            AnswerC
    FROM    dbo.CGO_TriviaQuestionIMP
    WHERE   ImportID = @ImportID
            AND AnswerC IS NOT NULL
            AND AnswerC <> ''
    UNION
    SELECT  @QuestionID ,
            AnswerD
    FROM    dbo.CGO_TriviaQuestionIMP
    WHERE   ImportID = @ImportID
            AND AnswerD IS NOT NULL
            AND AnswerD <> ''
  END
  
  UPDATE dbo.CGO_TriviaQuestionIMP
  SET Imported = 1
  WHERE ImportID = @ImportID
END
	
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO